#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3 as sqlite
import re
import time


from collections import defaultdict
from RGM import RGM as rgm
from helpers import get_sorted_tuple as gst, get_sorted_tuple_n as gstn

def generate_items_tables():
    conn = sqlite.connect('items_index.db')
    c = conn.cursor()

    # список подписок [(uid, item), ]
    subscriptions = rgm
    
    ### ************** generate subscriptions table **********************************    
    c.executemany("insert into subscriptions (uid, item) values (?, ?)", subscriptions)    
    conn.commit()
    ### ************** generate subscriptions table **********************************

    # инвертированный словарь {item: set([uid1, uid2, ]), }
    rev_subscriptions = defaultdict(set)
    for (uid, item) in subscriptions:
        rev_subscriptions[item].add(uid)
    
    # items, для которых более 5 uid
    mini_rev_subscriptions = {}
    for item in rev_subscriptions.keys():
        if len(rev_subscriptions[item]) > 5:
            mini_rev_subscriptions[item] = rev_subscriptions[item]
    # print 'len = %s' % len(mini_rev_subscriptions)
    
    ### ************** generate item_n table **********************************
    items_n = []
    for i in mini_rev_subscriptions.keys():
        items_n.append((i, len(mini_rev_subscriptions[i])))
    
    c.executemany("insert into items_n (item, n) values (?, ?)", items_n)
    conn.commit()
    ### ************** generate item_n table **********************************
    
    ### **************** generate item_to_item table **************************
    it_to_it_matrix = {}
    items = mini_rev_subscriptions.keys()
    l = len(items)
    k = 0
    ins_items = []
    for i in xrange(l):
        if i % 100 == 0:
            print i
        for j in xrange(i+1, l):
            it1, it2 = items[i], items[j]
            n = len(mini_rev_subscriptions[it1].intersection(mini_rev_subscriptions[it2]))
            if n > 0:
                if k % 100000 == 0:
                    c.executemany("insert into items (item1, item2, n) values (?, ?, ?)", ins_items)
                    conn.commit()
                    ins_items = []

                ins_items.append(gstn(it1, it2, n))
                k+=1
    
    c.executemany("insert into items (item1, item2, n) values (?, ?, ?)", ins_items)
    conn.commit()
    ### **************** generate item_to_item table **************************
    
    c.close()


